create external table jms_dm.dm_terminal_sign_punctuality_rate_dt 
(
end_agent_code                      string COMMENT '末端网点所属代理区编码' ,
end_agent_name                      string COMMENT '末端网点所属代理区名称' ,
end_provider_id                     string COMMENT '末端网点所属省份id' ,
end_provider_name                   string COMMENT '末端网点所属省份名称' ,
end_city_id                         string COMMENT '末端网点所属城市id' ,
end_city_name                       string COMMENT '末端网点所属城市名称' ,
end_area_id                         string COMMENT '末端网点所属区县id' ,
end_area_name                       string COMMENT '末端网点所属区县名称' ,
end_franchisee_code                 string COMMENT '末端网点所属加盟商编码' ,
end_franchisee_name                 string COMMENT '末端网点所属加盟商名称' ,
final_sign_network_code             string COMMENT '最终签收派件网点编码' ,
final_sign_network_name             string COMMENT '最终签收派件网点名称' ,
final_sign_user_code                string COMMENT '最后签收派件员编码' ,
final_sign_user_name                string COMMENT '最后签收派件员名称' ,
need_sign_count                     bigint COMMENT '应签收汇总' ,
all_sign_22                         string COMMENT '22点前全量签收量' ,
all_sign_24                         string COMMENT '24点前全量签收量' ,

need_sign_cnt_1030                  string COMMENT '10:30应签收量' ,
aging_sign_cnt_1030                 string COMMENT '10:30准点签收量' ,
aging_sign_over_time_1030_22        string COMMENT '10:30 22点前延误签收量' ,
aging_sign_over_time_1030_24        string COMMENT '10:30 22-24点延误签收量' ,
aging_nosign_cnt_1030               string COMMENT '10:30未签收量' ,

need_sign_cnt_1130                  string COMMENT '11:30应签收量' ,
aging_sign_cnt_1130                 string COMMENT '11:30准点签收量' ,
aging_sign_over_time_1130_22        string COMMENT '11:30 22点前延误签收量' ,
aging_sign_over_time_1130_24        string COMMENT '10:30 22-24点延误签收量' ,
aging_nosign_cnt_1130               string COMMENT '11:30未签收量收量' ,

need_sign_cnt_1400                  string COMMENT '14:00应签收量' ,
aging_sign_cnt_1400                 string COMMENT '14:00准点签收量' ,
aging_sign_over_time_1400_22        string COMMENT '14:00 22点前延误签收量' ,
aging_sign_over_time_1400_24        string COMMENT '14:00 22-24点延误签收量' ,
aging_nosign_cnt_1400               string COMMENT '14:00未签收量收量' ,

need_sign_cnt_1800                  string COMMENT '18:00应签收量' ,
aging_sign_cnt_1800                 string COMMENT '18:00准点签收量' ,
aging_sign_over_time_1800_22        string COMMENT '18:00 22点前延误签收量' ,
aging_sign_over_time_1800_24        string COMMENT '18:00 22-24点延误签收量' ,
aging_nosign_cnt_1800               string COMMENT '18:00未签收量收量' ,

need_sign_cnt_2200                  string COMMENT '22:00应签收量' ,
aging_sign_cnt_2200                 string COMMENT '22:00准点签收量' ,
aging_sign_over_time_2200_22        string COMMENT '22:00 22点前延误签收量' ,
aging_sign_over_time_2200_24        string COMMENT '22:00 22-24点延误签收量' ,
aging_nosign_cnt_2200               string COMMENT '22:00未签收量收量',
date_time                           date comment'业务日期',
order_source_name string comment'订单来源',
order_source_code string comment '订单来源code',
        end_brand_code string comment '共配code',
        end_brand_name string comment '共配名字',
      yizhan_sum int comment'驿站'
     ,guiji_sum int comment'柜机'
     ,shsm_sum int comment'送货上门'
     ,township_sum int comment'乡镇件'
     ,shift_1_need_count int comment'一班应签收量'
     ,shift_1_sign int comment'一班签收量'
     ,shift_2_need_count int comment'二班应签收量'
     ,shift_2_sign int comment' 二班签收量'
     ,earliest_deliver_time string comment'最早出仓时间'
     ,last_deliver_time     string comment'最晚出仓时间'
     ,is_shaidan  int comment '是否筛单网点'
     ,is_delay   int comment '是否顺延网点'
     ,is_new_open  int comment '是否新开网点'
     ,is_special_report  int comment '是否特殊上报网点'
     ,actual_sign_22_count  int comment '22点签收量'
     , actual_sign_00_count int comment '24点前前签收量'
    ,no_township_sum int comment '非乡镇件'  --
     , township_sign_sum int comment '乡镇件22点前签收量'  --
     , no_township_sign_sum int comment '非乡镇件22点前签收量'  --
)
comment '时效签收准点签收率报表新'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_terminal_sign_punctuality_rate_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');

alter table jms_dm.dm_terminal_sign_punctuality_rate_dt add columns (
      agent_need_count    int  comment'代理区应签收量',
    agent_aging_sign_num int  comment'代理区22点前时效签收量'
    ) cascade ;
